﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NUnit.Framework;
using System.Data.SqlServerCe;

namespace DbTests
{
    /// <summary>
    /// Tests for database
    /// </summary>
    [TestFixture]
    public class TestDb
    {
        private const string CONNECTION_STRING = @"Data Source = E:\soft\Project2\MvcApplication\App_Data\Jira113DB.sdf";

        private SqlCeConnection dbConnection;

        /// <summary>
        /// Connect to db before running tests
        /// </summary>
        [SetUp]
        public void Connect()
        {
            dbConnection = new SqlCeConnection(CONNECTION_STRING);
            dbConnection.Open();
        }

        /// <summary>
        /// Testting db connection
        /// </summary>
        [Test]
        public void TestConnect()
        {
            Assert.AreNotEqual(dbConnection, null);
        }

        /// <summary>
        /// Compare count of rows in 
        /// table Users before and after inserting
        /// </summary>
        [Test]
        public void UserCountAfterAddingTest()
        {
            const string userName = "TestUser";
            const string userSurname = "TestSurname";
            const string login = "TestLogin";
            const string password = "testpassword";
            const int role = 1;

            int countBeforeAdding = countUsersRow();
            
            // Inserting new user in users table
            string command = string.Format(
                "INSERT INTO Users (Name, Surname, Login, Password, DBAccessRole)" +
                "VALUES ('{0}','{1}','{2}','{3}', '{4}');",
                userName, userSurname, login, password, role);
            var sqlCommand = new SqlCeCommand(command, dbConnection);
            sqlCommand.ExecuteNonQuery();

            int countAfterAdding = countUsersRow();

            Assert.AreEqual(countBeforeAdding, countAfterAdding - 1);

            int id = selectIdOfLastUser();

            // delete tast data from table
            command = string.Format(
                "DELETE from Users where ID = '{0}'", id);
            sqlCommand = new SqlCeCommand(command, dbConnection);
            sqlCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Test inserting data with non-existent UserID in Roles 
        /// </summary>
        [Test]
        [ExpectedException(typeof(SqlCeException))]
        public void UserToRoleConnectingTest()
        {
            int userId = selectIdOfLastUser() + 1;
            const int projectId = 2;
            const int roleType = 1;

            // Inserting new role for user in project
            string command = string.Format(
                "INSERT INTO Roles (UserID, ProjectID, RoleType)" +
                "VALUES ('{0}','{1}','{2}');",
                userId, projectId, roleType);

            var sqlCommand = new SqlCeCommand(command, dbConnection);
            sqlCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Close connection after testing
        /// </summary>
        [TearDown]
        public void CloseConnection()
        {
            dbConnection.Dispose();
        }

        /// <summary>
        /// Calc count of row in Users table
        /// </summary>
        /// <returns>count of rows in table Users</returns>
        private int countUsersRow()
        {
            string command = "SELECT COUNT(*) FROM Users";
            var sqlCommandCount = new SqlCeCommand(command, dbConnection);

            return (int)sqlCommandCount.ExecuteScalar();
        }

        /// <summary>
        /// Get id of last added user in users table
        /// </summary>
        /// <returns>id of last user</returns>
        private int selectIdOfLastUser()
        {
            const string command = "SELECT TOP(1) ID FROM Users ORDER BY ID DESC";
            var sqlCommandCount = new SqlCeCommand(command, dbConnection);

            return (int)sqlCommandCount.ExecuteScalar();
        }
    }
}
